package cn.com.lms.business.learning.persistence.repository;

import cn.com.lms.business.learning.persistence.entity.FbLearningPlan;
import com.bnzj.core.persistence.repository.BaseRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public interface LearningPlanRepository extends BaseRepository<FbLearningPlan, Long> {


    @Modifying
    @Query(value = "update bnz_learning_plan set is_delete = 1 where id=?1", nativeQuery = true)
    void updateDelById(Long id);

    @Modifying
    @Query(value = "update bnz_learning_plan set is_over = 1 where id=?1", nativeQuery = true)
    void updateOverById(Long id);


    @Query(value = "select lp.id,lp.create_time as createTime,lp.finish_time as finishTime,lp.creator_name as createName,lp.score,lp.plan_name as planName,lp.cover_url as coverUrl,(select sum(course_time) from bnz_course_info where id in (select obj_id from bnz_learning_stage_detail where plan_id=lp.id and study_type=0)) as studyTime  ," +
            "(select count(DISTINCT lt.user_id) from bnz_learning_student lt where lt.plan_id=lp.id ) AS joinNum,a.finishNum AS finishNum " +
            " from bnz_learning_plan lp LEFT JOIN (SELECT   count( finishNum ) finishNum,b.plan_id " +
            " FROM (SELECT   lsd.plan_id,count( DISTINCT lsd.user_id ) AS finishNum,user_id " +
            " FROM   bnz_learning_student lsd " +
            " GROUP BY lsd.plan_id,   lsd.user_id " +
            " HAVING   ( SELECT count( user_id ) FROM bnz_learning_student WHERE finish_state = 1 AND plan_id = lsd.plan_id AND user_id = lsd.user_id )=(" +
            " SELECT   count( user_id ) FROM   bnz_learning_student    WHERE plan_id = lsd.plan_id    AND user_id = lsd.user_id ) ) b GROUP BY   b.plan_id    ) a ON lp.id = a.plan_id where lp.id = ?1", nativeQuery = true)
    Map getPlanDetailById(Long learningId);

    /**
     * 获取学习计划完成状态 1为则完成
     *
     * @param planId
     * @return
     */
    @Query(value = "select count(1)from(select count(1) stageNum,sum(case when a.joinNum=finishNum then 1 else 0 end ) finishNum " +
            "from (select count(DISTINCT ls.stage_detail_id) joinNum,sum(case when ls.finish_state=1 then 1 else 0 end) finishNum" +
            " FROM bnz_learning_plan lp left join bnz_learning_student ls on ls.plan_id=lp.id\twhere lp.is_delete=0 and lp.id=?1 " +
            "GROUP BY lp.id,ls.user_id) a) aa where aa.stageNum=aa.finishNum", nativeQuery = true)
    int getFinishState(Long planId);

    /**
     * 记录学员是否参加学习计划记录
     *
     * @return
     */
    @Query(value = "INSERT INTO bnz_learning_plan_join(`plan_id`, `user_id`, `creator`, `creator_name`, `modify_by`, `modify_name`) VALUES (?1, ?2, ?2, ?3, ?2, ?3) on DUPLICATE key update modify_time=now()", nativeQuery = true)
    @Modifying
    int savePlanJoinRecord(Long planId, Long userId, String userName);


    @Query(value = "SELECT ld.obj_id FROM bnz_learning_plan_join AS lj ,bnz_learning_stage_detail AS ld WHERE lj.plan_id = ?1 AND ld.plan_id = ?1 AND\n" +
            "lj.user_id = ?2 ", nativeQuery = true)
    List<Long> getObjIdList(Long learningId, Long userId);

    @Query(value = "SELECT ci.is_over FROM bnz_course_info AS ci WHERE ci.id = ?1", nativeQuery = true)
    String getCourseOver(Long courseId);
}

